<?php
//include('ganon.php');
include('simple_html_dom.php');

define('STARTTAG','<!-- S IINC -->');
define('ENDTAG','<!-- E IINC -->');
$date="";
define('DEFAULT_TRAINER_ID',1);

$htmlObj = new simple_html_dom();
$resultsURL = "http://news.bbc.co.uk/sport1/hi/other_sports/horse_racing/uk_results/default.stm";
$html = file_get_contents($resultsURL);
$file = "default-" . date("Y-m-d-H").".stm";
file_put_contents($file,$html);

$html = file_get_contents($file);
$startPos = strpos($html,STARTTAG);
$endPos = strpos($html,ENDTAG);
$fileLength = strlen($html);
$captureLength = ($endPos - $startPos) + strlen(ENDTAG);
$substr = substr ($html, $startPos,$captureLength );

//echo $substr, "\n";
echo $startPos , "\n";
echo $endPos, "\n";
echo $fileLength, "\n";
echo $captureLength, "\n";

// This array will contain all the meeting ids extracted, with thier position
$meetingArray = array();
$matchPattern = '/<!--Meeting ID: [0-9]+-->/';
preg_match_all($matchPattern,$substr,$meetingArray,PREG_OFFSET_CAPTURE);
$meetingArray = $meetingArray[0];
$link = db_connect();
// Build an array of meetings still as html
while($meetingElement = current($meetingArray)){
	$positionArray = array();
	$index = key($meetingArray);
	$eleStart = $meetingElement[1];
	$meetingElement = next($meetingArray);
	$eleEnd = $meetingElement[1];
	$meetingArray[$index][2] = substr ($substr, $eleStart,$eleEnd - $eleStart );
	// Load HTML from a string
	$htmlObj->load($meetingArray[$index][2]);
	
	$extractedDate = $htmlObj->find('h3[class=datebox]',0);
	if (is_object($extractedDate)){
	 $extractedDate = $htmlObj->find('h3[class=datebox]',0)->plaintext;
		if ($date != $extractedDate && $extractedDate != ""){
			$date = $extractedDate;
		}
	}

	if($htmlObj->find('h4[class=result]',0)){
		$course = $htmlObj->find('h4[class=result]',0)->plaintext;
	}

	if($htmlObj->find('div[class=addit]',0)){
		$going = $htmlObj->find('div[class=addit]',0)->plaintext;
		$going = substr ( $going , 7);
		//$going = substr_replace ($going,'' , 5);
	}
	
	if($htmlObj->find('div[class=extra]',0)){
		$extraText = $htmlObj->find('div[class=extra]',0)->plaintext;
		$pattern = '/Winning trainer:(.*)\./';
		preg_match($pattern, $extraText, $matches);
		$winningTrainer = $matches[0];
                list($junk, $winningTrainerName) = explode(':', $winningTrainer);
		$winningTrainerName = trim($winningTrainerName,'. ');  
		$trainer_id = upsertTrainer($winningTrainerName,$link);
		$positionArray['trainer_id'] = $trainer_id;
		//print "=============>" . $winningTrainerName . "\n";
	}
	//up insert Meeting and Course information
	$course_id = upsertCourse(array($course), $link);
	upsertMeeting(array($meetingArray[$index][1], $course_id, $date), $link);
	$positionArray['meeting_id'] = $meetingArray[$index][1];	
	// Now extract race info for each meeting
		
		foreach ($htmlObj->find('div[class=detail] strong',0) as $race){
			
			if(is_object($race)){
				// the race result will contain the race time, winner odds etc
				$raceResult = strip_tags($race->innertext);
				$pattern = '/([01]?[0-9]|2[0-3]):[0-5][0-9]/';
				if(preg_match($pattern, $raceResult, $matches)){
					$timeOfRace = $matches[0];
				}
			$going_id =  upsertGoing($going,$link);
			
			$pattern = '/[0-9]{1,2} ran/';
			$runners = "N/A";
			if(preg_match($pattern, $raceResult, $matches)){
				$runners = $matches[0];
			}
			//print $runners;
        		$race_id = upsertRace(array($course_id,$meetingArray[$index][1],$timeOfRace,$going_id,$runners,$meetingArray[$index][1],),$link);
		  	$positionArray['race_id'] = $race_id;	
			list($junk,$resultText) = explode('|', $raceResult);
			$horses = explode (';',$resultText);
			//print "\n\n";
			//print_r($horses);
			$finishCounter = 1;
			foreach($horses as $resultLine){
				// upsert Horses
				if (strstr($resultLine,$runners) || strstr($resultLine, 'Non-runners:') || $resultLine == " " || is_null($resultLine)){
					// Do Nothing
				}else{
					$pattern = '/[0-9]{1,2} (.*) \(/';
					preg_match($pattern, $resultLine, $matches);
					$horse = $matches[0]; 
					$horseName = trim($horse,'0123456789 ()');
					$horse_id = upsertHorse(trim($horseName), $link);
					$positionArray['horse_id'] = $horse_id;
					$pattern = '/\((.*)\)/';
					preg_match($pattern, $resultLine, $matches);
                                        $jockey = $matches[0];
					list($jockeyName,$odds) = explode(',', $jockey);
                                        $jockeyName = trim($jockeyName,'0123456789 ()');
					$jockey_id = upsertJockey(trim($jockeyName), $link); 
					$positionArray['jockey_id'] = $jockey_id;
					$positionArray['position'] = $finishCounter++;
					upsertPosition($positionArray,$link);
					// you only ever know the trainer of the winner
					$positionArray['trainer_id'] = DEFAULT_TRAINER_ID;
				}
			}
		}			
	}
	
}

function upsertRace($raceData,$link){
	$sql = "SELECT r.race_id as race_id FROM races r
		inner join courses c
		on r.course_id = c.course_id
		inner join meetings m
		on c.course_id = m.course_id
		Where m.meeting_id=\"{$raceData[1]}\" and m.course_id=\"{$raceData[0]}\"";

	$result = mysql_query($sql, $link);
	$row = mysql_fetch_assoc($result);
	$id = $row['race_id'];
	if(!$row){
		$sql = "INSERT INTO `races` " ;
		$sql .= " SET `race_time` = \"{$raceData[2]}\", ";
		$sql .= "`course_id` = {$raceData[0]}, ";
		$sql .= "`going_id` = {$raceData[3]}, ";
		$sql .= "`runners` = \"{$raceData[4]}\", ";
		$sql .= "`meeting_id` = {$raceData[5]}";

		$result = mysql_query($sql, $link);
		$id = mysql_insert_id();
	}else{
		$sql = "UPDATE `races` " ;
		$sql .= " SET `race_time` = \"{$raceData[2]}\", ";
		$sql .= "`course_id` = {$raceData[0]}, ";
		$sql .= "`going_id` = {$raceData[3]}, ";
		$sql .= "`runners` = \"{$raceData[4]}\", ";
		$sql .= "`meeting_id` = {$raceData[5]} ";
		$sql .= "WHERE `race_id` = $id";
		$result = mysql_query($sql, $link);
	}
	return $id;
}

function upsertMeeting($meetingData,$link){
	$sql = "SELECT `meeting_id` FROM `meetings` WHERE `meeting_id` = \"{$meetingData[0]}\"";
	$result = mysql_query($sql, $link);
	$row = mysql_fetch_assoc($result);
	if(!$row){
		$sql = "INSERT INTO `meetings` " ;
		$sql .= " SET `meeting_id` = {$meetingData[0]}, ";
		$sql .= "`course_id` =  {$meetingData[1]}, ";
		$sql .= "`meeting_date` = \"{$meetingData[2]}\"";
		$result = mysql_query($sql, $link);
	}else{
		$sql = "UPDATE `meetings` " ;
		$sql .= " SET `meeting_id` = {$meetingData[0]}, ";
		$sql .= "`course_id` =  {$meetingData[1]}, ";
		$sql .= "`meeting_date` = \"{$meetingData[2]}\" ";
		$sql .= "WHERE `meeting_id` = {$meetingData[0]}";
		$result = mysql_query($sql, $link);
	}
}

function upsertCourse($courseData,$link){
	$sql = "SELECT `course_id` FROM `courses` WHERE `course_name` = \"{$courseData[0]}\"";
	$result = mysql_query($sql, $link);
	$row = mysql_fetch_assoc($result);
	$id = $row['course_id'];
	if (!$row){
		$sql = "INSERT INTO `courses` SET ";
		$sql .= "`course_name` = \"{$courseData[0]}\"";
		$result = mysql_query($sql, $link);
		$id = mysql_insert_id();
	}else{
		$sql = "UPDATE `courses` ";
		$sql .= "(`course_name`) ";
		$sql .= "VALUES = (\"{$courseData[0]}\") ";
		$sql .= "WHERE `course_id` = $id";
		$result = mysql_query($sql, $link);
	}
	
	return $id;
	
}

function db_connect(){
	$config = parse_ini_file("config.ini",true);
//print_r($config);
	$link = mysql_connect($config['Database']['SERVER'], $config['Database']['USERNAME'], $config['Database']['PASSWORD']);
	if (!$link) {
		die('Could not connect: ' . mysql_error());
	}
	mysql_select_db('scraper');
	return $link;
}

function upsertGoing($going_text,$link){
	$sql="SELECT `going_id` FROM `going` WHERE `going_text` = \"$going_text\"";
	$result = mysql_query($sql, $link);
        $row = mysql_fetch_assoc($result);
	$id = $row['going_id'];
	if(!$row){
		// no going in going table so add it
		$sql = "INSERT INTO `going` SET ";
                $sql .= "`going_text` = \"$going_text\"";

		$result = mysql_query($sql, $link);
		$id = mysql_insert_id();

	}else{
		$sql = "UPDATE `going` SET";
		$sql .= "`going_text` = \"$going_text\" "; 
		$sql .= "WHERE `going_id` = $id";
		$result = mysql_query($sql, $link);
	}
	return $id;
	
}

function upsertHorse($horseName,$link){
	$horseName = trim($horseName, ". ()" );
        $sql="SELECT `horse_id` FROM `horses` WHERE `horse_name` = \"$horseName\"";
        $result = mysql_query($sql, $link);
        $row = mysql_fetch_assoc($result);
        $id = $row['horse_id'];
        if(!$row){
                // no Horse in table so add it
                $sql = "INSERT INTO `horses` SET ";
                $sql .= "`horse_name` = \"$horseName\"";

                $result = mysql_query($sql, $link);
                $id = mysql_insert_id();

        }else{
                $sql = "UPDATE `horses` SET";
                $sql .= "`horse_name` = \"$horseName\" ";
                $sql .= "WHERE `horse_id` = $id";
                $result = mysql_query($sql, $link);
        }
        return $id;

}

function upsertJockey($jockeyName,$link){
	$jockeyName = trim($jockeyName, ". ()" );
        $sql="SELECT `jockey_id` FROM `jockeys` WHERE `jockey_name` = \"$jockeyName\"";
        $result = mysql_query($sql, $link);
        $row = mysql_fetch_assoc($result);
        $id = $row['jockey_id'];
        if(!$row){
                // no going in going table so add it
                $sql = "INSERT INTO `jockeys` SET ";
                $sql .= "`jockey_name` = \"$jockeyName\"";

                $result = mysql_query($sql, $link);
                $id = mysql_insert_id();

        }else{
                $sql = "UPDATE `jockeys SET";
                $sql .= "`jockey_name` = \"$jockeyName\" ";
                $sql .= "WHERE `jockey_id` = $id";
                $result = mysql_query($sql, $link);
        }
        return $id;

}

function upsertTrainer($trainerName,$link){
	// Clean the trainerName string
	$trainerName = trim($trainerName, ". ()" );
        $sql="SELECT `trainer_id` FROM `trainers` WHERE `trainer_name` = \"$trainerName\"";
        $result = mysql_query($sql, $link);
        $row = mysql_fetch_assoc($result);
        $id = $row['trainer_id'];
        if(!$row){
                // no going in going table so add it
                $sql = "INSERT INTO `trainers` SET ";
                $sql .= "`trainer_name` = \"$trainerName\"";
                $result = mysql_query($sql, $link);
                $id = mysql_insert_id();

        }else{
                $sql = "UPDATE `trainers SET";
                $sql .= "`trainer_name` = \"$trainerName\" ";
                $sql .= "WHERE `trainer_id` = $id";
                $result = mysql_query($sql, $link);
        }
        return $id;

}

function upsertPosition($positionData,$link){
print_r($positionData);
        $sql = "SELECT r.race_id as race_id FROM scraper.races r
		inner join meetings m
		on m.meeting_id = r.meeting_id
		inner join positions p
		on r.race_id = p.race_id
		where m.meeting_id = {$positionData['meeting_id']} AND r.race_id = {$positionData['race_id']} AND p.position = {$positionData['position']};";
	//print $sql;
	$result = mysql_query($sql, $link);
        //if($result){
		$row = mysql_fetch_assoc($result);
	        $id = $row['race_id'];

		if(!$row){
                	// no going in going table so add it
	                $sql = "INSERT INTO `positions` SET ";
        	        $sql .= "`race_id`  = {$positionData['race_id']}, ";
			$sql .= "`horse_id` = {$positionData['horse_id']}, ";
			$sql .= "`trainer_id` = {$positionData['trainer_id']}, ";
			$sql .= "`jockey_id` = {$positionData['jockey_id']} ,";
			$sql .= "`position` = {$positionData['position']}";
	
			$result = mysql_query($sql, $link);
        	        $id = mysql_insert_id();

	        }	
//	}
        return $id;

}


?>
